﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;
using System.IO;

public partial class CongDem : System.Web.UI.Page
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmCongDem";
    static int f_SuDung = 0, f_Them = 0, f_Sua = 0, f_Xoa = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static DataTable dt, dtPrint;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])))
            gstGetMess("Hết phiên làm việc, vui lòng đăng nhập để tiếp tục", "Default.aspx");
        else
        {
            if (!IsPostBack)
            {
                string idnguoidung = Session["IDUser"].ToString();
                string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

                DataTable dt2 = ex.GetData_Text(sql);
                if (dt2.Rows.Count > 0)
                {
                    f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                    f_Them = int.Parse(dt2.Rows[0]["Them"].ToString());
                    f_Sua = int.Parse(dt2.Rows[0]["Sua"].ToString());
                    f_Xoa = int.Parse(dt2.Rows[0]["Xoa"].ToString());
                    f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                    f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                }
                if (f_SuDung.Equals(1))
                {
                    CurrentPage = 1;


                    Title = "Thiết lập chấm công đêm | " + Header;

                    

                    for (int i = 1; i <= 12; i++)
                    {
                        dlThangLoc.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
                    }
                    
                    dlThangLoc.SelectedValue = DateTime.Now.Month.ToString();

                    int year1 = DateTime.Now.Year - 3;
                    int year2 = DateTime.Now.Year;
                    for (int i = year2; i >= year1; i--)
                    {
                        dlNamLoc.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
                    }
                    dlNamLoc.SelectedValue = year2.ToString();

                    btnCancel_Click(sender, e);
                    LoadNhanVien(dlNhanVien);
                    LoadChucVu(dlChucVu);
                    LoadBoPhan(dlBoPhan);

                    LoadNhanVien(dlNhanVienLoc);
                    LoadChucVu(dlChucVuLoc);
                    LoadBoPhan(dlBoPhanLoc);
                }
                else
                    gstGetMess("Bạn không có quyền xem trang này", "Home.aspx");

            }
        }
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    void LoadNhanVien(DropDownList dl)
    {
        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_TLChamCong_ListNhanVien");
        dl.DataBind();
        dl.Items.Insert(0, new ListItem("--Chọn nhân viên--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadChucVu(DropDownList dlChucVu)
    {
        dlChucVu.Items.Clear();
        dlChucVu.DataSource = ex.GetData("sp_xdachucvu_ListDrop");
        dlChucVu.DataBind();
        dlChucVu.Items.Insert(0, new ListItem("--Chọn chức vụ--", ""));
        dlChucVu.SelectedIndex = 0;
    }
    void LoadBoPhan(DropDownList dlBoPhan)
    {
        dlBoPhan.Items.Clear();
        dlBoPhan.DataSource = ex.GetData("sp_xdabophan_ListDop");
        dlBoPhan.DataBind();
        dlBoPhan.Items.Insert(0, new ListItem("--Chọn bộ phận--", ""));
        dlBoPhan.SelectedIndex = 0;
    }
    protected void dlNhanVien_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            if (dlNhanVien.SelectedIndex > 0)
            {
                p = new SqlParameter[1];
                p[0] = new SqlParameter("@NhanVien", ex.GetGuid(dlNhanVien.SelectedValue));
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;

                DataTable d = ex.GetData("sp_xdachamcong_LoadBPCBByNV", p);
                if (d.Rows.Count > 0)
                {
                    dlBoPhan.SelectedValue = d.Rows[0][0].ToString();
                    dlChucVu.SelectedValue = d.Rows[0][1].ToString();
                }
            }
            else
            {
                dlBoPhan.SelectedIndex = 0;
                dlChucVu.SelectedIndex = 0;
            }
        }
        catch
        {
            dlBoPhan.SelectedIndex = 0;
            dlChucVu.SelectedIndex = 0;
        }
    }
    protected void txtSoGio_TextChanged(object sender, EventArgs e)
    {
        double s1 = double.Parse(txtSoGio.Text.Trim().Replace(",", "").Equals("") ? "0" : txtSoGio.Text.Trim().Replace(",", ""));
        double s2 = double.Parse(txtDonGia.Text.Trim().Replace(",", "").Equals("") ? "0" : txtDonGia.Text.Trim().Replace(",", ""));

        txtThanhTien.Text = ex.ConvertDecimal(s1 * s2);
        txtDonGia.Focus();
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (f_Them.Equals(1))
        {
            try
            {

                p = new SqlParameter[11];
                p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                p[1] = new SqlParameter("@Thang", int.Parse(txtTuNgay.Text.Trim().Split('/')[1]));
                p[1].SqlDbType = SqlDbType.Int;
                p[2] = new SqlParameter("@Nam", int.Parse(txtTuNgay.Text.Trim().Split('/')[2]));
                p[2].SqlDbType = SqlDbType.Int;
                p[3] = new SqlParameter("@NhanVien", ex.GetGuid(dlNhanVien.SelectedValue));
                p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                p[4] = new SqlParameter("@BoPhan", ex.GetGuid(dlBoPhan.SelectedValue));
                p[4].SqlDbType = SqlDbType.UniqueIdentifier;
                p[5] = new SqlParameter("@ChucVu", ex.GetGuid(dlChucVu.SelectedValue));
                p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                p[6] = new SqlParameter("@SoGio", double.Parse(txtSoGio.Text.Trim().Replace(",", "").Equals("") ? "0" : txtSoGio.Text.Trim()));
                p[6].SqlDbType = SqlDbType.Float;
                p[7] = new SqlParameter("@DonGia", double.Parse(txtDonGia.Text.Trim().Replace(",", "").Equals("") ? "0" : txtDonGia.Text.Trim()));
                p[7].SqlDbType = SqlDbType.Float;
                p[8] = new SqlParameter("@ThanhTien", double.Parse(txtThanhTien.Text.Trim().Replace(",", "").Equals("") ? "0" : txtThanhTien.Text.Trim()));
                p[8].SqlDbType = SqlDbType.Float;
                p[9] = new SqlParameter("@NoiDung", txtNoiDung.Text.Trim());
                p[9].SqlDbType = SqlDbType.NVarChar;
                p[10] = new SqlParameter("@ThoiGian", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                p[10].SqlDbType = SqlDbType.SmallDateTime;
                


                ex.ExecuteNonQuerry("sp_xdacongdem_Add", p);
                btnCancel_Click(sender, e);
                gstGetMess("Lưu thành công", "");
            }
            catch (Exception ax)
            {
                gstGetMess("Có lỗi trong quá trình lưu, xem trong file log", "");
                ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tThiết lập tiền công đêm\t" + ax.Message);
            }
        }
        else
            gstGetMess("Bạn không có quyền thêm dữ liệu trên trang này", "");
    }
    protected void btncapnhat_Click(object sender, EventArgs e)
    {
        if (f_Sua.Equals(1))
        {
            try
            {

                Guid gID = new Guid(hdID.Value);
                p = new SqlParameter[11];
                p[0] = new SqlParameter("@IDs", gID);
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                p[1] = new SqlParameter("@Thang", int.Parse(txtTuNgay.Text.Trim().Split('/')[1]));
                p[1].SqlDbType = SqlDbType.Int;
                p[2] = new SqlParameter("@Nam", int.Parse(txtTuNgay.Text.Trim().Split('/')[2]));
                p[2].SqlDbType = SqlDbType.Int;
                p[3] = new SqlParameter("@NhanVien", ex.GetGuid(dlNhanVien.SelectedValue));
                p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                p[4] = new SqlParameter("@BoPhan", ex.GetGuid(dlBoPhan.SelectedValue));
                p[4].SqlDbType = SqlDbType.UniqueIdentifier;
                p[5] = new SqlParameter("@ChucVu", ex.GetGuid(dlChucVu.SelectedValue));
                p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                p[6] = new SqlParameter("@SoGio", double.Parse(txtSoGio.Text.Trim().Replace(",", "").Equals("") ? "0" : txtSoGio.Text.Trim()));
                p[6].SqlDbType = SqlDbType.Float;
                p[7] = new SqlParameter("@DonGia", double.Parse(txtDonGia.Text.Trim().Replace(",", "").Equals("") ? "0" : txtDonGia.Text.Trim()));
                p[7].SqlDbType = SqlDbType.Float;
                p[8] = new SqlParameter("@ThanhTien", double.Parse(txtThanhTien.Text.Trim().Replace(",", "").Equals("") ? "0" : txtThanhTien.Text.Trim()));
                p[8].SqlDbType = SqlDbType.Float;
                p[9] = new SqlParameter("@NoiDung", txtNoiDung.Text.Trim());
                p[9].SqlDbType = SqlDbType.NVarChar;
                p[10] = new SqlParameter("@ThoiGian", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                p[10].SqlDbType = SqlDbType.SmallDateTime;
                

                ex.ExecuteNonQuerry("sp_xdacongdem_Update", p);
                btnCancel_Click(sender, e);
                gstGetMess("Lưu thành công", "");
            }
            catch (Exception ax)
            {
                gstGetMess("Có lỗi trong quá trình lưu, xem trong file log", "");
                ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tThiết lập tiền công đêm\t" + ax.Message);
            }
        }
        else
            gstGetMess("Bạn không có quyền thêm dữ liệu trên trang này", "");
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {
        dlNhanVien.SelectedIndex = 0;
        dlBoPhan.SelectedIndex = 0;
        dlChucVu.SelectedIndex = 0;
        txtSoGio.Text = "";
        txtDonGia.Text = "";
        txtThanhTien.Text = "";
        txtNoiDung.Text = "";
        txtTuNgay.Text = "";
        
        txtTuNgay.Enabled = true;
        hdID.Value = "";

        btnSave.Visible = true;
        btncapnhat.Visible = false;

        btnNext.Visible = true;
        btnPre.Visible = true;
        btnFirst.Visible = true;

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", 1);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdacongdem_List", p);
        GV.DataBind();
    }
    protected void GV_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        string ID = e.CommandArgument.ToString();
        hdID.Value = ID;
        Guid gID = new Guid(ID);
        p = new SqlParameter[1];
        p[0] = new SqlParameter("@IDs", gID);
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;

        if (e.CommandName.Equals("Select"))
        {
            DataTable dt = ex.GetData("sp_xdacongdem_ListByID", p);
            if (dt.Rows.Count > 0)
            {
                try
                {
                    dlBoPhan.SelectedValue = dt.Rows[0]["BoPhan"].ToString();
                    dlChucVu.SelectedValue = dt.Rows[0]["ChucVu"].ToString();
                    txtThanhTien.Text = dt.Rows[0]["ThanhTien"].ToString();
                    txtSoGio.Text = dt.Rows[0]["SoGio"].ToString();
                    txtDonGia.Text = dt.Rows[0]["DonGia"].ToString();
                    txtNoiDung.Text = dt.Rows[0]["NoiDung"].ToString();
                    txtTuNgay.Text = dt.Rows[0]["ThoiGian"].ToString();
                    dlNhanVien.SelectedValue = dt.Rows[0]["NhanVien"].ToString();
                }
                catch (Exception ax)
                {
                    gstGetMess("Có lỗi trong quá trình xem, xem trong file log", "");
                    ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tThiết lập tiền công đêm\t" + ax.Message);
                }

            }
            btncapnhat.Visible = true;
            btnSave.Visible = false;
            btncapnhat.Visible = CheckKhoa2(txtTuNgay.Text.Trim(), frmName, Session["user"].ToString());

            if (btncapnhat.Visible == false)
            {
                txtTuNgay.Enabled = false;
            }

        }
        else
            if (e.CommandName.Equals("Del"))
            {
                if (f_Xoa.Equals(1))
                {
                    try
                    {
                        ex.ExecuteNonQuerry("sp_xdacongdem_Del", p);
                        btnCancel_Click(sender, e);
                        gstGetMess("Xóa thành công", "");
                    }
                    catch (Exception ax)
                    {
                        ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tThiết lập tiền công đêm\t" + ax.Message);
                        gstGetMess(ax.Message, "");
                    }
                }
                else
                    gstGetMess("Bạn không có quyền xóa dữ liệu trên trang này", "");
            }
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", 1);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdacongdem_List", p);
        GV.DataBind();

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            p = new SqlParameter[2];
            p[0] = new SqlParameter("@PageNumber", CurrentPage);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;

            GV.DataSource = ex.GetData("sp_xdacongdem_List", p);
            GV.DataBind();

        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdacongdem_List", p);
        GV.DataBind();

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
    protected void dlLoaiTimKiem_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiTimKiem.SelectedIndex.Equals(0))
        {
            dlChucVuLoc.Visible = false;
            dlBoPhanLoc.Visible = false;
            dlNhanVienLoc.Visible = false;
        }
        else
            if (dlLoaiTimKiem.SelectedIndex.Equals(1))
            {
                dlChucVuLoc.Visible = false;
                dlBoPhanLoc.Visible = false;
                dlNhanVienLoc.Visible = true;
            }
            else if (dlLoaiTimKiem.SelectedIndex.Equals(2))
            {
                dlChucVuLoc.Visible = false;
                dlBoPhanLoc.Visible = true;
                dlNhanVienLoc.Visible = false;
            }
            else if (dlLoaiTimKiem.SelectedIndex.Equals(3))
            {
                dlChucVuLoc.Visible = true;
                dlBoPhanLoc.Visible = false;
                dlNhanVienLoc.Visible = false;
            }
    }
    protected void btnTimKiem_Click(object sender, EventArgs e)
    {
        if (dlLoaiTimKiem.SelectedIndex.Equals(0))
        {
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@Thang", int.Parse(dlThangLoc.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(dlNamLoc.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;

            GV.DataSource = ex.GetData("sp_xdacongdem_Search", p);
            GV.DataBind();
        }
        else if (dlLoaiTimKiem.SelectedIndex.Equals(1))
        {
            if (dlNhanVienLoc.SelectedIndex.Equals(0))
                gstGetMess("Chọn nhân viên", "");
            else
            {
                p = new SqlParameter[3];
                p[0] = new SqlParameter("@Thang", int.Parse(dlThangLoc.SelectedValue));
                p[0].SqlDbType = SqlDbType.Int;
                p[1] = new SqlParameter("@Nam", int.Parse(dlNamLoc.SelectedValue));
                p[1].SqlDbType = SqlDbType.Int;
                p[2] = new SqlParameter("@NhanVien", ex.GetGuid(dlNhanVienLoc.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;

                GV.DataSource = ex.GetData("sp_xdacongdem_SearchByNV", p);
                GV.DataBind();
            }
        }
        else if (dlLoaiTimKiem.SelectedIndex.Equals(2))
        {
            if (dlBoPhanLoc.SelectedIndex.Equals(0))
                gstGetMess("Chọn bộ phận", "");
            else
            {
                p = new SqlParameter[3];
                p[0] = new SqlParameter("@Thang", int.Parse(dlThangLoc.SelectedValue));
                p[0].SqlDbType = SqlDbType.Int;
                p[1] = new SqlParameter("@Nam", int.Parse(dlNamLoc.SelectedValue));
                p[1].SqlDbType = SqlDbType.Int;
                p[2] = new SqlParameter("@BoPhan", ex.GetGuid(dlBoPhanLoc.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;

                GV.DataSource = ex.GetData("sp_xdacongdem_SearchByBoPhan", p);
                GV.DataBind();
            }
        }
        else if (dlLoaiTimKiem.SelectedIndex.Equals(3))
        {
            if (dlChucVuLoc.SelectedIndex.Equals(0))
                gstGetMess("Chọn chức vụ", "");
            else
            {
                p = new SqlParameter[3];
                p[0] = new SqlParameter("@Thang", int.Parse(dlThangLoc.SelectedValue));
                p[0].SqlDbType = SqlDbType.Int;
                p[1] = new SqlParameter("@Nam", int.Parse(dlNamLoc.SelectedValue));
                p[1].SqlDbType = SqlDbType.Int;
                p[2] = new SqlParameter("@ChucVu", ex.GetGuid(dlChucVuLoc.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;

                GV.DataSource = ex.GetData("sp_xdacongdem_SearchByChucVu", p);
                GV.DataBind();
            }
        }
        btnPre.Visible = false;
        btnNext.Visible = false;
        btnFirst.Visible = false;
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            if (dlLoaiTimKiem.SelectedIndex.Equals(2) && dlBoPhanLoc.SelectedIndex > 0)
            {
                p = new SqlParameter[3];
                p[0] = new SqlParameter("@Thang", int.Parse(dlThangLoc.SelectedValue));
                p[0].SqlDbType = SqlDbType.Int;
                p[1] = new SqlParameter("@Nam", int.Parse(dlNamLoc.SelectedValue));
                p[1].SqlDbType = SqlDbType.Int;
                p[2] = new SqlParameter("@BoPhan", ex.GetGuid(dlBoPhanLoc.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;

                dtPrint = ex.GetData("sp_xdacongdem_PrintByBoPhan", p);
            }
            else
            {
                p = new SqlParameter[2];
                p[0] = new SqlParameter("@Thang", int.Parse(dlThangLoc.SelectedValue));
                p[0].SqlDbType = SqlDbType.Int;
                p[1] = new SqlParameter("@Nam", int.Parse(dlNamLoc.SelectedValue));
                p[1].SqlDbType = SqlDbType.Int;
                dtPrint = ex.GetData("sp_xdacongdem_Print", p);
            }

            PrintNPOI(dtPrint);
        }
        else gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Báo cáo tiền công đêm";

            if (dlLoaiTimKiem.SelectedIndex.Equals(2) && dlBoPhanLoc.SelectedIndex > 0)
                sheetname = dlBoPhanLoc.SelectedItem.Text;

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            //hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;
            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThangLoc.SelectedItem.Text.ToUpper() + " " + dlNamLoc.SelectedItem.Text.ToUpper();
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BÁO CÁO TIỀN CÔNG ĐÊM " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 9);
            sheet.AddMergedRegion(cra);

            //Bộ phận
            rowIndex++;
            if (dlLoaiTimKiem.SelectedIndex.Equals(2) && dlBoPhanLoc.SelectedIndex > 0)
            {
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue(dlLoaiTimKiem.SelectedIndex.Equals(2) && dlBoPhanLoc.SelectedIndex > 0 ? "Bộ phận: " + dlBoPhanLoc.SelectedItem.Text : "");
                r1c1.CellStyle = hs3;
                r1c1.Row.Height = 500;

                cra = new CellRangeAddress(3, 3, 0, 9);
                sheet.AddMergedRegion(cra);
            }

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2
            rowIndex++;
            rowIndex++;
            //header1
            row = sheet.CreateRow(rowIndex);
            string[] header1 = { "STT","Ngày tháng", "Họ và tên", "Bộ phận", "Chức vụ","Nội dung", "Số giờ làm việc", "Đơn giá", "Thành tiền","Ký nhận" };

            //tao header1
            for (int m = 0; m <= 9; m++)
            {
                r1c1 = row.CreateCell(m);
                r1c1.SetCellValue(header1[m]);
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 400;
            }


            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue((i + 1).ToString());
                r1c1.CellStyle = hsCenter;

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    r1c1 = row.CreateCell(j + 1);
                    if (j >= 5 && j<=7)
                    {
                        if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            r1c1.SetCellType(CellType.NUMERIC);
                            r1c1.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                        }

                        r1c1.CellStyle = j == 5 ? hsCenter : hsRight;
                    }
                    else
                    {
                        r1c1.SetCellValue(dt.Rows[i][j].ToString());
                        r1c1.CellStyle = headerLabelCellStyle;
                    }
                    r1c1.Row.Height = 500;
                }
                r1c1 = row.CreateCell(9);
                r1c1.SetCellValue("");
                r1c1.CellStyle = headerLabelCellStyle;
            }
            for (int ik = 0; ik <= 9; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }

            //end sheet tong hop--------------------------
            #region Footer
            //-----------Footer

            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            r1c1 = row.CreateCell(1);
            r1c1.SetCellValue("Tổng cộng");
            r1c1.Row.Height = 500;
            r1c1.CellStyle = hs4;

            r1c1 = row.CreateCell(6);
            r1c1.CellFormula = "Sum(G7:G" + rowIndex.ToString() + ")";
            r1c1.Row.Height = 500;
            r1c1.CellStyle = hs4;

            r1c1 = row.CreateCell(8);
            r1c1.CellFormula = "Sum(I7:I" + rowIndex.ToString() + ")";
            r1c1.Row.Height = 500;
            r1c1.CellStyle = hs4;

            //fix lai border
            for (int h = 0; h <= 9; h++)
            {
                if (h == 1 || h == 6 || h == 8)
                {

                }
                else
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }
            #endregion


            //in tung bo phan vao tung sheet
            #region BoPhan
            string bienso = "";
            if (!dlLoaiTimKiem.SelectedIndex.Equals(2))
            {

                int STT = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        bienso = dt.Rows[i]["BoPhan"].ToString();

                        if (!bienso.Equals(dt.Rows[i - 1]["BoPhan"].ToString()))
                        {
                            STT = 0;

                            #region Footer
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            r1c1 = row.CreateCell(1);
                            r1c1.SetCellValue("Tổng cộng");
                            r1c1.Row.Height = 500;
                            r1c1.CellStyle = hs4;

                            r1c1 = row.CreateCell(6);
                            r1c1.CellFormula = "Sum(G7:G" + rowIndex.ToString() + ")";
                            r1c1.Row.Height = 500;
                            r1c1.CellStyle = hs4;

                            r1c1 = row.CreateCell(8);
                            r1c1.CellFormula = "Sum(I7:I" + rowIndex.ToString() + ")";
                            r1c1.Row.Height = 500;
                            r1c1.CellStyle = hs4;

                            //fix lai border
                            for (int h = 0; h <= 9; h++)
                            {
                                if (h == 1 || h == 6 || h == 8)
                                {

                                }
                                else
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            for (int ik = 0; ik <= 9; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }

                            #endregion
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(bienso));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO TIỀN CÔNG ĐÊM " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 9);
                            sheet.AddMergedRegion(cra);

                            //Bộ phận
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Bộ phận: " + bienso);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 9);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2
                            rowIndex++;
                            rowIndex++;
                            //header1
                            row = sheet.CreateRow(rowIndex);

                            //tao header1
                            for (int m = 0; m <= 9; m++)
                            {
                                r1c1 = row.CreateCell(m);
                                r1c1.SetCellValue(header1[m]);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }


                            #endregion
                            STT++;

                        }
                    }
                    else
                    {
                        bienso = "";
                        if (!bienso.Equals(dt.Rows[i]["BoPhan"].ToString()))
                        {
                            bienso = dt.Rows[i]["BoPhan"].ToString();
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(bienso));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO TIỀN CÔNG ĐÊM " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 9);
                            sheet.AddMergedRegion(cra);

                            //Bộ phận
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Bộ phận: " + bienso);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 9);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2
                            rowIndex++;
                            rowIndex++;
                            //header1
                            row = sheet.CreateRow(rowIndex);

                            //tao header1
                            for (int m = 0; m <= 9; m++)
                            {
                                r1c1 = row.CreateCell(m);
                                r1c1.SetCellValue(header1[m]);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }


                            #endregion
                        }
                    }
                    #endregion

                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    r1c1 = row.CreateCell(0);
                    r1c1.SetCellValue((STT).ToString());
                    r1c1.CellStyle = hsCenter;

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        r1c1 = row.CreateCell(j + 1);
                        if (j >= 5 && j <= 7)
                        {
                            if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                            {
                                r1c1.SetCellType(CellType.NUMERIC);
                                r1c1.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                            }

                            r1c1.CellStyle = j == 5 ? hsCenter : hsRight;
                        }
                        else
                        {
                            r1c1.SetCellValue(dt.Rows[i][j].ToString());
                            r1c1.CellStyle = headerLabelCellStyle;
                        }
                        r1c1.Row.Height = 500;
                    }
                    r1c1 = row.CreateCell(9);
                    r1c1.SetCellValue("");
                    r1c1.CellStyle = headerLabelCellStyle;
                    #region Footer
                    if (i == dt.Rows.Count - 1)
                    {

                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        r1c1 = row.CreateCell(1);
                        r1c1.SetCellValue("Tổng cộng");
                        r1c1.Row.Height = 500;
                        r1c1.CellStyle = hs4;

                        r1c1 = row.CreateCell(6);
                        r1c1.CellFormula = "Sum(G7:G" + rowIndex.ToString() + ")";
                        r1c1.Row.Height = 500;
                        r1c1.CellStyle = hs4;

                        r1c1 = row.CreateCell(8);
                        r1c1.CellFormula = "Sum(I7:I" + rowIndex.ToString() + ")";
                        r1c1.Row.Height = 500;
                        r1c1.CellStyle = hs4;

                        //fix lai border
                        for (int h = 0; h <= 9; h++)
                        {
                            if (h == 1 || h == 6 || h == 8)
                            {

                            }
                            else
                            {
                                r1c1 = row.CreateCell(h);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                        }
                        for (int ik = 0; ik <= 9; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                }
            }
            #endregion
            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string filename = dlLoaiTimKiem.SelectedIndex.Equals(2) && dlBoPhanLoc.SelectedIndex > 0 ? dlBoPhanLoc.SelectedItem.Text : "";
                string saveAsFileName = "BCCongDem-" + filename + " " + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }
        else
            gstGetMess("Không có bản ghi nào để in", "");
    }
    protected void txtTuNgay_TextChanged(object sender, EventArgs e)
    {
        if (hdID.Value.Equals(""))
            btnSave.Visible = CheckKhoa2(txtTuNgay.Text.Trim(), frmName, Session["user"].ToString());
        else
            btncapnhat.Visible = CheckKhoa2(txtTuNgay.Text.Trim(), frmName, Session["user"].ToString());

    }
    private bool CheckKhoa2(string ngaythang, string name, string nhanvien)
    {
        if (!ngaythang.Trim().Equals(""))
        {

            p = new SqlParameter[3];
            p[0] = new SqlParameter("@Name", name);
            p[0].SqlDbType = SqlDbType.NVarChar;
            p[1] = new SqlParameter("@Ngay", ex.GetNgayThang(ngaythang));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@NhanVien", nhanvien);
            p[2].SqlDbType = SqlDbType.NVarChar;

            string index = ex.GetValueString("sp_KhoaMo_Check", p);

            if (index.Trim().Equals("2"))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        else return false;
    }
}